package com.zlj.jdbc.utils;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.junit.Test;

public class TestJdbcUtils {
	
	/*
	 * 测试
	 */
	@Test
	public void test01() throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		
		//Employee employee = new Employee();
		
		//测试增加操作
		/*
			employee.setId(6);
			employee.setName("李四");
			employee.setEmail("lisi@163.com");
			employee.setGender("male");
			insertEmployee(employee);
		*/
		
		//测试修改操作
		/*
			employee.setId(6);
			employee.setName("wangwu");
			updateEmployee(employee);
		*/
		
		//测试删除
		//deleteEmployee(8);
		
		//测试单笔查询操作
		/*Map<String, Object> selectSingle = selectSingle(7);
		for(Entry<String, Object> map : selectSingle.entrySet()) {
			String name = map.getKey();
			Object value = map.getValue();
			System.out.println(name + "==" + value);
		}*/
		
		//测试多笔查询操作
		/*
		List<Map<String,Object>> selectMore = selectMore();
		for(Map<String,Object> list : selectMore) {
			for(Entry<String, Object> map : list.entrySet()) {
				String name = map.getKey();
				Object value = map.getValue();
				System.out.println(name + "==" + value);
			}
		}
		*/
		
		//测试通过反射查询单笔数据
		Employee employee = selectRefSingle(2);
		System.out.println(employee);  //Employee [id=2, name=jack, email=jack@163.com, gender=m]
		
		System.out.println("---------------");
		
		//通过反射测试多笔数据查询
		List<Employee> list = selectRefMore();
		System.out.println(list);
		for (Employee employee2 : list) {
			System.out.println(employee2);
		}
	}
	
	/*
	 * 测试增加
	 */
	public void insertEmployee(Employee employee) throws SQLException {
		String sql = "insert into employee values(?,?,?,?)";
		Object[] params = {employee.getId(), employee.getName(), employee.getEmail(), employee.getGender()};
		JdbcUtils.getInstance().update(sql, params);
	}
	
	/*
	 * 测试修改
	 */
    public void updateEmployee(Employee employee) throws SQLException {
    	String sql = "update employee set name = ? where id = ?";
    	Object[] params = {employee.getName(), employee.getId()};
    	JdbcUtils.getInstance().update(sql, params);
    }
    
    /*
	 * 测试删除
	 */
    public void deleteEmployee(int id) throws SQLException {
    	String sql = "delete from employee where id = ?";
    	Object[] params = {id};
    	JdbcUtils.getInstance().update(sql, params);
    }
    
    /*
     * 测试单笔查询操作
     */
    public Map<String, Object> selectSingle(int id) throws SQLException {
    	String sql = "select * from employee where id = ?";
    	Object[] params = {id};
    	Map<String, Object> map = JdbcUtils.getInstance().findSingleResult(sql, params);
    	return map;
    }
    
    /*
     * 测试多笔查询操作
     */
    public List<Map<String,Object>> selectMore() throws SQLException {
    	String sql = "select * from employee";
    	Object[] params = {};
    	List<Map<String,Object>> list = JdbcUtils.getInstance().findMoreResult(sql, params);
    	return list;
    }
    
    /*
     * 测试通过反射查询单笔数据
     */
    public Employee selectRefSingle(int id) throws InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException, SQLException {
    	Employee employee;
    	String sql = "select * from employee where id = ?";
    	Object[] params = {id};
    	employee = JdbcUtils.getInstance().findSingleRefResult(sql, Employee.class, params);
    	return employee;
    }
    
    /*
     * 测试通过反射查询多笔数据
     */
    public List<Employee> selectRefMore() throws InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException, SQLException {
    	String sql = "select * from employee";
    	Object[] params = {};
    	List<Employee> list = JdbcUtils.getInstance().findMoreRefResult(sql, Employee.class, params);
    	return list;
    }
}
